USE [texchange_db]
GO

/****** Object:  StoredProcedure [dbo].[SP_GetAllCompany]    Script Date: 05/29/2011 19:39:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[SP_GetAllCompany] 	
	@lang bigint,
	@start  bigint=0,
	@limit bigint=30,
	@order nvarchar(10) = 'id',
	@orderdir nvarchar(4) = 'desc',
	@name nvarchar(50) = null,
	@catid int = null,
	@ttype int = null,
	@countryid int = null,
	@member_type_id int = null
	-- Add the parameters for the function here 
AS
BEGIN 
	Declare @SQLQuery AS NVarchar(4000)
	SET @SQLQuery = 'SELECT DISTINCT(id), name, country_id, created, country_name, City, operating_activity, type_company_id,companyType, member_type_id FROM 
			(SELECT DISTINCT(Company.id), Company.name, Company.country_id, Company.created, Places.name as country_name,  
			Company.City, Company.operating_activity, Company.type_company_id, UniversalType.name as companyType,  
			Company.member_type_id, ROW_NUMBER() OVER (ORDER BY Company.' + @order + ' '+ @orderdir +') AS rownum FROM Company 
			INNER JOIN Places ON Company.country_id = Places.id '
	IF @lang = 1
	BEGIN
		SET @SQLQuery = @SQLQuery + ' INNER JOIN UniversalType ON Company.type_company_id = UniversalType.id '
	END
	ELSE
	BEGIN
		SET @SQLQuery = @SQLQuery + ' INNER JOIN UniversalType ON (Company.type_company_id = UniversalType.parent_id AND UniversalType.language_id=@lang) '
	END
	IF @ttype Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' INNER JOIN CJCompanyTransport ON Company.id=CJCompanyTransport.company_id'
	END
	--SET @SQLQuery = @SQLQuery + ' INNER JOIN CJCompanyTransport ON Company.id=CJCompanyTransport.company_id WHERE 1=1'
	SET @SQLQuery = @SQLQuery + ' WHERE 1=1'
	
	IF @catid Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.type_company_id=@catid '
	END
	IF @name Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.name LIKE @name '
	END
	IF @ttype Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND CJCompanyTransport.transporttype_id = @ttype '
	END
	IF @countryid Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.country_id = @countryid '
	END
	
	IF @member_type_id Is Not Null
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.member_type_id = @member_type_id '
	END
	
	SET @SQLQuery = @SQLQuery + ') as a WHERE rownum >=@start AND rownum<=@limit'
	Declare @ParamDefinition AS NVarchar(2000) 
	Set @ParamDefinition = ' @lang bigint,
			@start  bigint=0,
			@limit bigint=30,
			@order nvarchar(10),
			@orderdir nvarchar(4),
			@name nvarchar(50),
			@catid int = null,
			@ttype int = null,
			@countryid int = null,
			@member_type_id = null'
	PRINT @SQLQuery
	--INSERT INTO @Company
	Execute sp_Executesql @SQLQuery, @ParamDefinition,@lang,@start,@limit,@order,@orderdir,@name,@catid,@ttype,@countryid
	RETURN
END

GO


